<?php
//Yii::import('application.vendors.mpdf.mPDF');
//require_once(Yii::getPathOfAlias('application.extensions.PHPExcel') . '.php');
Yii::import('application.components.U');
class ReportController extends GxController
{
    private $tbs;
    public function init()
    {
        parent::init();
        Yii::import('application.components.tbs_class_php5', true);
        Yii::import('application.components.tbs_plugin_excel', true);
        $this->tbs = new clsTinyButStrong;
        $this->tbs->PlugIn(TBS_INSTALL, TBS_EXCEL);
        ini_set('error_reporting', E_ERROR);
    }
    public function actionNotaDebet()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $supplier_id = $_POST['supplier_id'];
        $salesman_id = $_POST['salesman_id'];
        $supplier_name = 'Semua';
        if ($supplier_id != null) {
            $supplier = Supplier::model()->findByPk($supplier_id);
            $supplier_name = $supplier->supplier_name;
        }
        $salesman_name = 'Semua';
        if ($salesman_id != null) {
            $salesman = Salesman::model()->findByPk($salesman_id);
            $salesman_name = $supplier->supplier_name;
        }
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'nota_debet.xml');
        $notadebet = U::report_nota_debet($from, $to, $supplier_id, $salesman_id);
        $header[] = array('from' => $from, 'to' => $to,
            'pemasok' => $supplier_name, 'salesman' => $salesman_name);
        $this->tbs->MergeBlock('header', $header);
        $this->tbs->MergeBlock('notadebet', $notadebet);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "nota_debet$from-$to-$supplier_name-$salesman_name.xls");
    }
    public function actionSedangPesan()
    {
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'sedang_pesan.xml');
        $notadebet = U::report_sedang_pesan();
        $header[] = array('tgl' => date('d M Y'));
        $this->tbs->MergeBlock('header', $header);
        $this->tbs->MergeBlock('pesan', $notadebet);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "BarangSedangDipesan.xls");
    }
    public function actionSetoranKas()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $salesman_id = $_POST['salesman_id'];
        $salesman = Salesman::model()->findByPk($salesman_id);
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'setoran_kas.xml');
        $notadebet = U::report_setoran_kas($from, $to, $salesman_id);
        $user = $this->loadModel(Yii::app()->user->getId(), "Users");
        $header[] = array(
            'sales' => $salesman->salesman_name,
            'user' => $user->username,
            'tgl' => date('d M Y'),
            'jam' => date('H:m:i'),
        );
        $this->tbs->MergeBlock('header', $header);
        $this->tbs->MergeBlock('ks', $notadebet);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "SetoranKas.xls");
    }
    public function actionPersediaanHabis()
    {
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'persediaan_habis.xml');
        $min = U::report_barang_habis();
        $header[] = array('tgl' => date('d M Y'));
        $this->tbs->MergeBlock('header', $header);
        $this->tbs->MergeBlock('min', $min);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "PersediaanBarangHabis.xls");
    }
    public function actionUtang()
    {
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'utang.xml');
        $utang = U::report_utang();
        $header[] = array('tgl' => date('d M Y'));
        $this->tbs->MergeBlock('header', $header);
        $this->tbs->MergeBlock('utang', $utang);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "Utang" . $header[0]['tgl'] . ".xls");
    }
    public function actionPembelian()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $supplier_id = $_POST['supplier_id'];
        $supplier_name = 'Semua';
        if ($supplier_id != null) {
            $supplier = Supplier::model()->findByPk($supplier_id);
            $supplier_name = $supplier->supplier_name;
        }
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'pembelian.xml');
        $beli = U::report_pembelian($from, $to, $supplier_id);
        $this->tbs->MergeBlock('header', array(array('from' => $from, 'to' => $to, 'pemasok' => $supplier_name)));
        $this->tbs->MergeBlock('beli', $beli);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "Pembelian" . $from . $to . ".xls");
    }
    public function actionCetakBarang()
    {
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'barang.xml');
        $beli = U::cetak_master_barang();
        $this->tbs->MergeBlock('b', $beli);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "MasterBarang.xls");
    }
    public function actionMutasiKas()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $bank_id = $_POST['id_bank'];
        $bank = Bank::model()->findByPk($bank_id);
        $saldo = $bank->saldo_before($from);
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'mutasi_kas.xml');
        $kas = U::report_mutasi_bank($from, $to, $bank_id);
        $new_kas = array();
        global $systypes_array;
        foreach ($kas as $row) {
            if ($row['Keterangan'] == '') {
                $row['Keterangan'] = $systypes_array[$row['type']];
            }
            $row['awal'] = $saldo;
            $saldo += $row['amount'];
            $row['akhir'] = $saldo;
            $new_kas[] = $row;
        }
        $this->tbs->MergeBlock('header', array(array('from' => $from, 'to' => $to, 'bank' => $bank->nama_akun)));
        $this->tbs->MergeBlock('kas', $new_kas);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "mutasi_kas" . $from . $to . ".xls");
    }
    public function actionJurnalUmum()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'jurnal_umum.xml');
        $ju = U::report_jurnal_umum($from, $to);
        $this->tbs->MergeBlock('header', array(array('from' => $from, 'to' => $to)));
        $this->tbs->MergeBlock('ju', $ju);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "jurnal_umum" . $from . $to . ".xls");
    }
    public function actionInventoryCard()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $from = $_POST['tglfrom'];
            $to = $_POST['tglto'];
            $barang_id = $_POST['barang_id'];
            $gudang_id = $_POST['gudang_id'];
            $barang = Barang::model()->findByPk($barang_id);
            $saldo_awal = StockMoves::get_saldo_item_before($barang_id, $from, $gudang_id);
            $row = U::report_kartu_stok($barang_id, $from, $to, $gudang_id);
            $stock_card = array();
            $begin_arr = array('tgl' => '', 'tipe_name' => 'Saldo Awal',
                'in' => $saldo_awal >= 0 ? $saldo_awal : '',
                'out' => $saldo_awal < 0 ? -$saldo_awal : '',
                'after' => $saldo_awal, 'doc_ref' => ''
            );
            $stock_card[] = $begin_arr;
            foreach ($row as $newrow) {
                $newrow['before'] = $saldo_awal;
                $newrow['after'] += $newrow['before'] + $newrow['qty'];
                $saldo_awal = $newrow['after'];
                $stock_card[] = $newrow;
            }
            $begin_arr = array('tgl' => '', 'tipe_name' => 'Saldo Akhir',
                'in' => $saldo_awal >= 0 ? $saldo_awal : '',
                'out' => $saldo_awal < 0 ? -$saldo_awal : '',
                'after' => $saldo_awal, 'doc_ref' => ''
            );
            $stock_card[] = $begin_arr;
            $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'kartu_stok.xml');
            $this->tbs->MergeField('header', array('from' => sql2date($from, 'dd MMM yyyy'),
                'to' => sql2date($to, 'dd MMM yyyy'), 'barcode' => $barang->barcode,
                'barang_name' => $barang->barang_name,
            ));
            $this->tbs->MergeBlock('ks', $stock_card);
            $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "kartu_stok" . $barang->barcode . $from . $to . ".xls");
//            $dataProvider = new CArrayDataProvider($stock_card, array(
//                'id' => 'InventoryCard',
//                'pagination' => false
//            ));
//            if ($this->format == 'excel') {
//                header('Content-type: application/vnd.xls');
//                header("Content-Disposition: attachment; filename=InventoryCard$from-$to.xls");
//                echo $this->render('InventoryCard', array('dp' => $dataProvider,
//                    'from' => sql2date($from, 'dd MMM yyyy'),
//                    'to' => sql2date($to, 'dd MMM yyyy'),
//                    'item' => $barang
//                ), true);
////                $this->TBS->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'kartu_stok.xml');
//            } else {
////                $this->TBS->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'kartu_stok.htm');
//                $this->render('InventoryCard', array('dp' => $dataProvider,
//                    'from' => sql2date($from, 'dd MMM yyyy'),
//                    'to' => sql2date($to, 'dd MMM yyyy'),
//                    'item' => $barang,
//                    'store' => $store
//                ));
//            }
        }
    }
    public function actionGeneralLedger()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $from = $_POST['tglfrom'];
            $to = $_POST['tglto'];
            $coa = $_POST['account_code'];
            $chart_account = ChartMaster::model()->findByPk($coa);
            $result = U::get_general_ledger($coa, $from, $to);
            $begin = U::get_gl_before($coa, $from);
            $begin_arr = array('tgl' => '', 'tipe_name' => 'Saldo Awal',
                'Debit' => $begin >= 0 ? $begin : '',
                'Credit' => $begin < 0 ? -$begin : '',
                'Balance' => $begin,
                'amount' => $begin,
                'reference' => ''
            );
            $gl[] = $begin_arr;
            foreach ($result as $newrow) {
                $newrow['Balance'] = $newrow['amount'] + $begin;
//                $newrow['tgl'] = sql2date($newrow['tgl'], 'dd MMM yyyy');
                $begin = $newrow['Balance'];
                $gl[] = $newrow;
            }
            $begin_arr = array('tgl' => '', 'tipe_name' => 'Saldo Akhir',
                'Debit' => $begin >= 0 ? $begin : '',
                'Credit' => $begin < 0 ? -$begin : '',
                'Balance' => $begin,
                'amount' => $begin,
                'reference' => ''
            );
            $gl[] = $begin_arr;
            $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'gl.xml');
            $this->tbs->MergeField('header', array('from' => sql2date($from, 'dd MMM yyyy'),
                'to' => sql2date($to, 'dd MMM yyyy'),
                'account_name' => $chart_account->account_name
            ));
            $this->tbs->MergeBlock('gl', $gl);
            $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "gl" . $chart_account->account_code . $from . $to . ".xls");
        }
    }
    public function actionKelolaStok()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'LapPersediaan.xml');
        $ks = U::report_pengelolaan_persediaan($from, $to);
        $this->tbs->MergeBlock('header', array(array('from' => $from, 'to' => $to)));
        $this->tbs->MergeBlock('ks', $ks);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "LapPersediaan" . $from . $to . ".xls");
    }
    public function actionSelisihStok()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'LapSelisihPersediaan.xml');
        $ks = U::report_selisih_persediaan($from, $to);
        $this->tbs->MergeBlock('header', array(array('from' => $from, 'to' => $to)));
        $this->tbs->MergeBlock('ks', $ks);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "LapSelisihPersediaan" . $from . $to . ".xls");
    }
    private function labarugi($from, $to)
    {
        $lr = ChartMaster::get_laba_rugi($from, $to);
        $jual_sales = $jual_agen = $hpp = $beban_administrasi = $beban_pemasaran
            = $pendapatan_lain = $beban_lain = $pajak = array();
        $total_sales = $total_agen = $total_hpp = $total_beban_administrasi
            = $total_beban_pemasaran = $total_pendapatan_lain = $total_beban_lain
            = $total_pajak = 0;
        foreach ($lr as $chart) {
            if (preg_match('/^4[1-3].*/', $chart['account_code'])) {
                $chart['total'] = -$chart['total'];
                $total_sales += $chart['total'];
                $jual_sales[] = $chart;
            } elseif (preg_match('/^4[4-6].*/', $chart['account_code'])) {
                $chart['total'] = -$chart['total'];
                $total_agen += $chart['total'];
                $jual_agen[] = $chart;
            } elseif (preg_match('/^5\d.*/', $chart['account_code'])) {
                $total_hpp += $chart['total'];
                $hpp[] = $chart;
            } elseif (preg_match('/^61\d.*/', $chart['account_code'])) {
                $total_beban_administrasi += $chart['total'];
                $beban_administrasi[] = $chart;
            } elseif (preg_match('/^62\d.*/', $chart['account_code'])) {
                $total_beban_pemasaran += $chart['total'];
                $beban_pemasaran[] = $chart;
            } elseif (preg_match('/^7\d.*/', $chart['account_code'])) {
                $chart['total'] = -$chart['total'];
                $total_pendapatan_lain += $chart['total'];
                $pendapatan_lain[] = $chart;
            } elseif (preg_match('/^8\d.*/', $chart['account_code'])) {
                $chart['total'] = -$chart['total'];
                $total_beban_lain += $chart['total'];
                $beban_lain[] = $chart;
            } elseif (preg_match('/^9\d.*/', $chart['account_code'])) {
                $chart['total'] = -$chart['total'];
                $total_pajak += $chart['total'];
                $pajak[] = $chart;
            }
        }
        $total_hpp = -$total_hpp;
        $total_jual = $total_sales + $total_agen;
        $laba_kotor = $total_jual + $total_hpp;
        $total_beban_operasional = $total_beban_administrasi + $total_beban_pemasaran;
        $total_beban_operasional = -$total_beban_operasional;
        $laba_operasional = $laba_kotor + $total_beban_operasional;
        $pendapatan_beban_diluar = $total_pendapatan_lain + $total_beban_lain;
        $laba_bersih = $laba_operasional + $pendapatan_beban_diluar;
        $laba = $laba_bersih + $total_pajak;
        return array('header' => array(array(
            'from' => $from, 'to' => $to, 'total_sales' => $total_sales, 'total_agen' => $total_agen,
            'total_jual' => $total_jual, 'total_hpp' => $total_hpp, 'laba_kotor' => $laba_kotor,
            'total_beban_administrasi' => $total_beban_administrasi, 'total_beban_pemasaran' => $total_beban_pemasaran,
            'total_beban_operasional' => $total_beban_operasional, 'laba_operasional' => $laba_operasional,
            'pendapatan_beban_diluar' => $pendapatan_beban_diluar, 'laba_bersih' => $laba_bersih, 'laba' => $laba)),
            'sales' => $jual_sales, 'agen' => $jual_agen, 'hpp' => $hpp, 'beban_administrasi' => $beban_administrasi,
            'beban_pemasaran' => $beban_pemasaran, 'pendapatan_lain' => $pendapatan_lain, 'beban_lain' => $beban_lain,
            'pajak' => $pajak
        );
    }
    public function actionCheckNeraca()
    {
        if (!Yii::app()->request->isAjaxRequest) $this->redirect(url('/'));
        if (isset($_POST) && !empty($_POST)) {
            $tglto = $_POST['tglto'];
            $tglfrom = DateTime::createFromFormat('Y-m-d\TH:i:s', $tglto)->format('Y-01-01');
            $tgl = DateTime::createFromFormat('Y-m-d\TH:i:s', $tglto)->format('Y-m-d');
            $bulan = (float)DateTime::createFromFormat('Y-m-d\TH:i:s', $tglto)->format('m');;
            $tahun = DateTime::createFromFormat('Y-m-d\TH:i:s', $tglto)->format('Y');;
            $cmd = Yii::app()->db->createCommand("
            SELECT MONTH(tran_date) bulan FROM psn_gl_trans pgt
            WHERE pgt.type = :laba_rigu AND pgt.tran_date > :from
            AND pgt.tran_date <= :to
            GROUP BY MONTH(tran_date)");
            $result = $cmd->queryColumn(array(':from' => $tglfrom, ':to' => $tgl, ':laba_rigu' => LABARUGI));
            for ($i = 1; $i <= $bulan; $i++) {
                if (!in_array($i, $result)) {
                    echo CJSON::encode(array(
                        'success' => false,
                        'msg' => 'Laba rugi ' . DateTime::createFromFormat('Y-m-d', "$tahun-$i-01")->format('F Y') . ' belum dibuat.'));
                    Yii::app()->end();
                }
            }
            echo CJSON::encode(array(
                'success' => true,
                'msg' => 'OK'));
        }
    }
    public function actionNeraca()
    {
        $to = $_POST['tglto'];
        $strtotime = strtotime($to);
        $from = date('Y-01-01', $strtotime);
        $neraca = ChartMaster::get_neraca($from, $to);
        $aset_lancar = $aset_tetap = $utang_lancar = $utang_jangka_panjang = $modal = $coa_tmp = $total_tmp = array();
        $total_aset_lancar = $total_aset_tetap = $total_utang_lancar = $total_utang_jangka_panjang
            = $total_modal = 0;
        foreach ($neraca as $chart) {
            if (preg_match('/^11.*/', $chart['account_code'])) {
//                $chart['total'] = -$chart['total'];
                $total_aset_lancar += $chart['total'];
                $aset_lancar[] = $chart;
            } elseif (preg_match('/^12.*/', $chart['account_code'])) {
                $key = $chart['account_code'][2];
                if (preg_match('/^12.0.*/', $chart['account_code'])) {
                    $coa_tmp[$key] = $chart['account_name'];
                    $total_tmp[$key] += $chart['total'];
                } else {
                    $total_tmp[$key] += $chart['total'];
                }
                $total_aset_tetap += $chart['total'];
//                $chart['total'] = -$chart['total'];
//                $total_aset_tetap += $chart['total'];
            } elseif (preg_match('/^21\d.*/', $chart['account_code'])) {
                $chart['total'] = -$chart['total'];
                $total_utang_lancar += $chart['total'];
                $utang_lancar[] = $chart;
            } elseif (preg_match('/^22\d.*/', $chart['account_code'])) {
                $chart['total'] = -$chart['total'];
                $total_utang_jangka_panjang += $chart['total'];
                $utang_jangka_panjang[] = $chart;
            } elseif (preg_match('/^3\d.*/', $chart['account_code'])) {
                //khusus prive 34000 dibalik
//                if ($chart['account_code'] == '34000') {
                $chart['total'] = -$chart['total'];
//                }
                $total_modal += $chart['total'];
                $modal[] = $chart;
            }
        }
        foreach ($coa_tmp as $keys => $value) {
            $aset_tetap[] = array('account_name' => $value, 'total' => $total_tmp[$keys]);
        }
        $total_aset = $total_aset_tetap + $total_aset_lancar;
        $total_utang = $total_utang_lancar + $total_utang_jangka_panjang;
        $total_utang_modal = $total_utang + $total_modal;
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'neraca.xml');
        $this->tbs->MergeBlock('header', array(array('to' => sql2date($to), 'total_aset_lancar' => $total_aset_lancar,
            'total_utang_lancar' => $total_utang_lancar, 'total_utang_jangka_panjang' => $total_utang_jangka_panjang,
            'total_modal' => $total_modal, 'total_utang' => $total_utang, 'total_utang_modal' => $total_utang_modal,
            'total_aset_tetap' => $total_aset_tetap, 'total_aset' => $total_aset
        )));
        $this->tbs->MergeBlock('aset_lancar', $aset_lancar);
        $this->tbs->MergeBlock('aset_tetap', $aset_tetap);
        $this->tbs->MergeBlock('utang_lancar', $utang_lancar);
        $this->tbs->MergeBlock('utang_jangka_panjang', $utang_jangka_panjang);
        $this->tbs->MergeBlock('modal', $modal);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "Neraca" . $from . ".xls");
    }
    public function actionGenerateLabaRugi()
    {
        $month = $_POST['month'];
        $year = $_POST['year'];
        $d = new DateTime("$year-$month-01");
        $from = $d->format('Y-m-d');
        $to = $d->format('Y-m-t');
        try {
            $result = self::labarugi($from, $to);
            $amount = $result['header'][0]['laba'];
            $amount = -$amount;
            /* @var $gl GlTrans */
            $gl = GlTrans::model()->find('type = :type AND tran_date >= :from AND tran_date <= :to',
                array(':type' => LABARUGI, ':from' => $from, ':to' => $to));
            if ($gl == null) {
                $id = U::get_next_trans(LABARUGI);
                Yii::import('application.components.Gl');
                $glr = new Gl();
                $glr->add_gl(LABARUGI, $id, $to, null, COA_LABA_RUGI, 'PROFIT LOST', '', $amount);
            } else {
                $gl->amount = $amount;
                if (!$gl->save())
                    throw new Exception("Gagal menyimpan laba rugi. " . CHtml::errorSummary($gl));
            }
            $msg = 'Profit Lost succesfully generated.';
            $status = true;
        } catch (Exception $ex) {
            $status = false;
            $msg = $ex->getMessage();
        }
        echo CJSON::encode(array(
                'success' => $status,
                'msg' => $msg)
        );
        Yii::app()->end();
    }
    public function actionLabaRugi()
    {
        $from = $_POST['from_date'];
        $to = $_POST['to_date'];
        $lb = self::labarugi($from, $to);
        $this->tbs->LoadTemplate(Yii::getPathOfAlias('application.views.reports') . DIRECTORY_SEPARATOR . 'LapLabaRugi.xml');
        $this->tbs->MergeBlock('header', $lb['header']);
        $this->tbs->MergeBlock('sales', $lb['sales']);
        $this->tbs->MergeBlock('agen', $lb['agen']);
        $this->tbs->MergeBlock('hpp', $lb['hpp']);
        $this->tbs->MergeBlock('beban_administrasi', $lb['beban_administrasi']);
        $this->tbs->MergeBlock('beban_pemasaran', $lb['beban_pemasaran']);
        $this->tbs->MergeBlock('pendapatan_lain', $lb['pendapatan_lain']);
        $this->tbs->MergeBlock('beban_lain', $lb['beban_lain']);
        $this->tbs->MergeBlock('pajak', $lb['pajak']);
        $this->tbs->Show(TBS_EXCEL_DOWNLOAD, "LapLabaRugi" . $from . $to . ".xls");
    }
    public function actionDosSementara()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $tgl = $_POST['tgl'];
            $sales = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $sales_name = str_replace(" ", "", $sales->salesman_name);
            $criteria = new CDbCriteria();
            $criteria->addCondition("salesman_id = :salesman_id");
            $criteria->addCondition("tgl = :tgl");
            $criteria->params = array(':salesman_id' => $_POST['salesman_id'], ':tgl' => $_POST['tgl']);
            $dos = Dos::model()->find($criteria);
            $detils = U::get_dos_detil($dos->dos_id);
            $start = 1;
            $file_name = "DOSementara_" . $sales_name . "_" . $tgl;
            $worksheet_name = 'DOSementara';
            $objPHPExcel = new PHPExcel();
            $this->header($objPHPExcel, $start, $worksheet_name,
                "DO SEMENTARA");
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", "Tanggal: " . sql2long_date($tgl))
                ->getStyle("A$start")->getFont()->setSize(12)->setBold(false);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start",
                    "Salesman: " . $sales->salesman_name)
                ->getStyle("A$start")->getFont()->setSize(12)->setBold(false);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", "No DOS: " . $dos->doc_ref)
                ->getStyle("A$start")->getFont()->setSize(12)->setBold(false);
            $objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
            $start++;
            $start++;
            $start_body = $start;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", "No")
                ->setCellValue("B$start", "Kode")->setCellValue("C$start", "Nama Barang")
//                ->setCellValue("D$start", "JML")->setCellValue("E$start","SAT")
                ->setCellValue("D$start", "PCS")->getStyle("A$start:F$start")
                ->getFont()->setBold(true);
            $start++;
            $total_persen = 0;
            $no = 1;
            foreach ($detils as $row) {
                $jml = $format == 'excel' ? $row['jml'] : acc_format($row['jml']);
                $pcs = $format == 'excel' ? $row['pcs'] : acc_format($row['pcs']);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", $no)
                    ->setCellValueExplicit("B$start", $row['barcode'], PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue("C$start", $row['barang_name'])
//                    ->setCellValue("D$start", $jml)
//                    ->setCellValue("E$start",$row['sat'])
                    ->setCellValue("D$start", $pcs);
                $no++;
                $start++;
            }
            $end_body = $start - 1;
            $styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start_body:D$end_body")->applyFromArray($styleArray);
            $start_row = $start_body + 1;
            if ($format == 'excel') {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("B$start_row:B$end_body")->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("C$start_row:C$end_body")->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
            } else {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("B$start_body:C$end_body")->getAlignment()
                    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("A")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("B")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("C")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("D")->setAutoSize(true);
//            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("E")->setAutoSize(true);
//            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("F")->setAutoSize(true);
            if ($format == 'excel') {
                self::footerExcel($objPHPExcel, $file_name);
            } else {
                self::footerPdf($objPHPExcel, 'A4', $file_name, "DO Sementara");
            }
//            $this->footer($objPHPExcel, 'A4', $start, $file_name, $format,
//                "DO Sementara");
            Yii::app()->end();
        }
    }
    protected function header(&$objPHPExcel, &$start, $worksheet_name,
                              $report_title, $papersize = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4)
    {
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);
        $objPHPExcel->setActiveSheetIndex(0)->getPageSetup()->setPaperSize($papersize);
        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setLeft(0);
        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setRight(0);
        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setTop(0);
        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setBottom(0);
        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setHeader(0);
        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setFooter(0);
//        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setLeft(0.1 / 2.54);
//        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setRight(0.1 / 2.54);
//        $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setTop(0.1 / 2.54);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
            ->setCellValueExplicit("A$start",
                $report_title, PHPExcel_Cell_DataType::TYPE_STRING)
            ->getStyle("A$start")->getFont()->setSize(14)->setBold(false);
        $start++;
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
            ->setCellValue("A$start", app()->params['corp']['nama']);
        $start++;
        $objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
    }
    protected function footer($objPHPExcel, $paper_size, $start, $file_name,
                              $format, $html_title)
    {
        $start++;
        $user = $this->loadModel(Yii::app()->user->getId(), "Users");
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
            ->setCellValue("A$start", "Dicetak oleh: " . $user->username);
        $start++;
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
            ->setCellValue("A$start",
                "Pada tanggal " . get_date_today('dd/MM/yyyy') . " jam " . get_time_now());
        ob_end_clean();
        ob_start();
        if ($format == 'excel') {
            header('Content-Type: application/vnd.ms-excel');
            header("Content-Disposition: attachment;filename=$file_name.xls");
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');
        } else {
            $objPHPExcel->getActiveSheet()->setShowGridlines(false);
            $mPDF1 = new mPDF('', $paper_size);
            $objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
            $header = $objWriter->generateHTMLHeader(true);
            $header = str_replace("<body>", "<body onload='window.print();'>",
                $header);
            $header = str_replace("Untitled Spreadsheet", $html_title, $header);
            $html = $header . $objWriter->generateStyles(true) . $objWriter->generateSheetData() .
                $objWriter->generateHTMLFooter();
            $mPDF1->WriteHTML($html);
            $mPDF1->Output("$file_name.pdf", 'I');
        }
    }
    public function actionSetorKasSales()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $tgl = $_POST['tgl'];
            $sales = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $sales_name = str_replace(" ", "", $sales->salesman_name);
            $criteria = new CDbCriteria();
            $criteria->addCondition("salesman_id = :salesman_id");
            $criteria->addCondition("tgl = :tgl");
            $criteria->params = array(':salesman_id' => $_POST['salesman_id'], ':tgl' => $_POST['tgl']);
            $setor_kas = SetorKasSales::model()->find($criteria);
            $start = 1;
            $file_name = "SetoranKasPerSales_" . $sales_name . "_" . $tgl;
            $worksheet_name = 'SetoranKasPerSales';
            $objPHPExcel = new PHPExcel();
            $this->header($objPHPExcel, $start, $worksheet_name,
                "SETORAN KAS PER SALESMAN");
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", "Tanggal: " . sql2long_date($tgl))
                ->getStyle("A$start")->getFont()->setSize(12)->setBold(false);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start",
                    "Salesman: " . $sales->salesman_name)
                ->getStyle("A$start")->getFont()->setSize(12)->setBold(false);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", "No SKS: " . $setor_kas->doc_ref)
                ->getStyle("A$start")->getFont()->setSize(12)->setBold(false);
            $objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
            $start++;
            $start++;
            $start_body = $start;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", "Uang Muka Faktur")
                ->setCellValue("B$start", $format == 'excel' ? $setor_kas->faktur : acc_format($setor_kas->faktur));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", "Pelunasan Piutang")
                ->setCellValue("B$start", $format == 'excel' ? $setor_kas->pelunasan_piutang : acc_format($setor_kas->pelunasan_piutang));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", "Retur Botol")
                ->setCellValue("B$start", $format == 'excel' ? $setor_kas->retur_botol : acc_format($setor_kas->retur_botol));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", "Biaya Sales")
                ->setCellValue("B$start", $format == 'excel' ? $setor_kas->biaya_sales : acc_format($setor_kas->biaya_sales));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", "Retur Penjualan Tunai")
                ->setCellValue("B$start", $format == 'excel' ? $setor_kas->retur_penjualan_tunai : acc_format($setor_kas->retur_penjualan_tunai));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A$start", "Kas Disetor")
                ->setCellValue("B$start", $format == 'excel' ? $setor_kas->kas_disetor : acc_format($setor_kas->kas_disetor))->getStyle("A$start:B$start")
                ->getFont()->setBold(true);
            $start++;
            $end_body = $start - 1;
            $styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start_body:B$end_body")->applyFromArray($styleArray);
            $start_row = $start_body;
            if ($format == 'excel') {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("B$start_row:B$end_body")->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
            } else {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("B$start_body:B$end_body")->getAlignment()
                    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("A")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("B")->setAutoSize(true);
            $this->footer($objPHPExcel, 'A4', $start, $file_name, $format,
                "Setoran Kas Per Salesman");
            Yii::app()->end();
        }
    }
    public function actionLapPiutangSales()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $tgl = $_POST['tgl'];
            $salesman = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $detils = CJSON::decode($_POST['detil']);
            $start = 1;
            $file_name = "DaftarPiutangPerSalesman_" . $salesman->salesman_name . "_$tgl";
            $worksheet_name = 'DaftarPiutangPerSalesman';
            $objPHPExcel = new PHPExcel();
            PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
//            $objPHPExcel->getDefaultStyle()->getFont()->setName('Courier New');
            $this->header($objPHPExcel, $start, $worksheet_name,
                "DAFTAR PIUTANG PER SALESMAN");
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Tanggal')
                ->setCellValue("B$start", ': ' . $tgl)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Salesman')
                ->setCellValue("B$start", ': ' . $salesman->salesman_name)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'No.')
                ->setCellValue("B$start", "No. Faktur")
                ->setCellValue("C$start", "Tanggal")
                ->setCellValue("D$start", "Kode")
                ->setCellValue("E$start", "Nama Konsumen")
                ->setCellValue("F$start", "Nilai Faktur")
                ->setCellValue("G$start", "Sisa Tagihan")
                ->getStyle("A$start:G$start")->getFont()->setSize(8)->setBold(true);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:G$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:G$start")->applyFromArray($borderbottom);
            $row_num = 1;
            $start_row = $start + 1;
            foreach ($detils as $detil) {
                if ($detil['cetak']) {
                    $start++;
                    $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue("A$start", $row_num)
                        ->setCellValueExplicit("B$start", $detil['no_faktur'], PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValue("C$start", $detil['tgl'])
                        ->setCellValueExplicit("D$start", $detil['konsumen_code'], PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("E$start", $detil['konsumen_name'], PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValue("F$start", $format == 'excel' ? $detil['total'] : acc_format($detil['total']))
                        ->setCellValue("G$start", $format == 'excel' ? $detil['sisa'] : acc_format($detil['sisa']))
                        ->getStyle("A$start:G$start")->getFont()->setSize(8)->setBold(false);
                    $row_num++;
                }
            }
            $end_body = $start;
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:G$start")->applyFromArray($borderbottom);
            $start++;
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("B$start", 'Salesman, ')
                ->setCellValue("E$start", 'Admin,')
                ->getStyle("A$start:E$start")->getFont()->setSize(8)->setBold(false);
            $start++;
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("B$start", '(' . $salesman->salesman_name . ')')
                ->setCellValue("E$start", '(................)')
                ->getStyle("A$start:E$start")->getFont()->setSize(8)->setBold(false);
            if ($format == 'excel') {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("C$start_row:C$end_body")->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("F$start_row:G$end_body")->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
            } else {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle("E$start_row:F$end_body")->getAlignment()
                    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("A")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("B")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("C")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("D")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("E")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("F")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("G")->setAutoSize(true);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, $worksheet_name);
            }
            Yii::app()->end();
        }
    }
    public function actionLapBiayaSales()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $drtgl = $_POST['drtgl'];
            $ketgl = $_POST['ketgl'];
            $salesman = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $start = 1;
            $file_name = "DaftarBiayaPerSalesman_" . $salesman->salesman_name;
            $worksheet_name = 'DaftarBiayaPerSalesman';
            $objPHPExcel = new PHPExcel();
            PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
//            $objPHPExcel->getDefaultStyle()->getFont()->setName('Courier New');
            $this->header($objPHPExcel, $start, $worksheet_name,
                "DAFTAR BIAYA PER SALESMAN");
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Dari Tanggal')
                ->setCellValue("B$start", ': ' . $drtgl)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Sampai Tanggal')
                ->setCellValue("B$start", ': ' . $ketgl)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Salesman')
                ->setCellValue("B$start", ': ' . $salesman->salesman_name)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $row = U::get_report_biaya_sales($drtgl, $ketgl, $_POST['salesman_id']);
            $header = array_keys($row[0]);
            $jml_header = count($header);
            $col_end = chr(64 + $jml_header);
            $start_row = $start + 1;
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING)
                    ->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
            }
            $start_body = $start_row;
            for ($r = 0; $r < count($row); $r++) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->fromArray($row[$r], null, "A$start_body");
                $start_body++;
            }
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                if ($header[$i] == 'BBM' || $header[$i] == 'Parkir'
                    || $header[$i] == 'Makan' || $header[$i] == 'Lain-lain'
                ) {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    }
                } elseif ($header[$i] == 'Tanggal') {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
                    }
                } else {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    }
                }
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, $worksheet_name);
            }
            Yii::app()->end();
        }
    }
    public function actionLapBotolSales()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $drtgl = $_POST['drtgl'];
            $ketgl = $_POST['ketgl'];
            $salesman = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $start = 1;
            $file_name = "DaftarBotolPerSalesman_" . $salesman->salesman_name;
            $worksheet_name = 'DaftarBotolPerSalesman';
            $objPHPExcel = new PHPExcel();
            PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
//            $objPHPExcel->getDefaultStyle()->getFont()->setName('Courier New');
            $this->header($objPHPExcel, $start, $worksheet_name,
                "DAFTAR BOTOL PER SALESMAN");
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Dari Tanggal')
                ->setCellValue("B$start", ': ' . $drtgl)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Sampai Tanggal')
                ->setCellValue("B$start", ': ' . $ketgl)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("G$start:J$start")
                ->setCellValue("A$start", 'Salesman')
                ->setCellValue("B$start", ': ' . $salesman->salesman_name)
                ->getStyle("A$start:B$start")->getFont()->setSize(8)->setBold(true);
            $start++;
            $row = U::get_report_botol_sales($drtgl, $ketgl, $_POST['salesman_id']);
            $header = array_keys($row[0]);
            $jml_header = count($header);
            $col_end = chr(64 + $jml_header);
            $start_row = $start + 1;
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING)
                    ->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
            }
            $start_body = $start_row;
            for ($r = 0; $r < count($row); $r++) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->fromArray($row[$r], null, "A$start_body");
                $start_body++;
            }
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                if ($header[$i] == 'Kas Keluar') {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    }
                } elseif ($header[$i] == 'Tanggal') {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
                    }
                } else {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    }
                }
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, $worksheet_name);
            }
            Yii::app()->end();
        }
    }
    protected function footerExcel($objPHPExcel, $file_name)
    {
        ob_end_clean();
        ob_start();
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$file_name.xls");
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
    protected function footerPdf($objPHPExcel, $paper_size, $file_name, $html_title, $orientasi = 'P')
    {
        $objPHPExcel->getActiveSheet()->setShowGridlines(false);
        $mPDF1 = new mPDF('c', $paper_size, 10, 'Arial', 5, 5, 5, 2, 0, 0, $orientasi);
        $mPDF1->SetDisplayMode('fullpage');
        $objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
        $header = $objWriter->generateHTMLHeader(true);
        $header = str_replace("<body style='background-color:white;'>", "<body onload='window.print();'>",
            $header);
        $header = str_replace("Untitled Spreadsheet", $html_title, $header);
        $html = $header . $objWriter->generateStyles(true) . $objWriter->generateSheetData() .
            $objWriter->generateHTMLFooter();
        $mPDF1->WriteHTML($html);
        $mPDF1->Output("$file_name.pdf", 'I');
    }
    public function actionLapPenjualan()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $supplier_id = null;
            $salesman_id = null;
            $pasar_id = null;
            $konsumen_id = null;
            $jnslap = $_POST['jnslap'];
            $format = $_POST['format'];
            $to_date = $_POST['to_date'];
            $from_date = $_POST['from_date'];
            if (isset($_POST['supplier_id'])) {
                $supplier_id = $_POST['supplier_id'];
            }
            if (isset($_POST['supplier'])) {
                $supplier = $_POST['supplier'];
            }
            if (isset($_POST['salesman_id'])) {
                $salesman_id = $_POST['salesman_id'];
            }
            if (isset($_POST['salesman'])) {
                $salesman = $_POST['salesman'];
            }
            if (isset($_POST['pasar_id'])) {
                $pasar_id = $_POST['pasar_id'];
            }
            if (isset($_POST['pasar'])) {
                $pasar = $_POST['pasar'];
            }
            if (isset($_POST['konsumen_id'])) {
                $konsumen_id = $_POST['konsumen_id'];
            }
            if (isset($_POST['konsumen'])) {
                $konsumen = $_POST['konsumen'];
            }
            $jnstrans = $_POST['jnstrans'];
            $objPHPExcel = null;
            switch ($jnslap) {
                case 'LPP' :
                    $file_name = 'LapKuantitasPenj';
                    $objPHPExcel = self::LapKuantitasPenj($format, $from_date, $to_date, $jnstrans, false,
                        $supplier_id, $salesman_id, $pasar_id, $konsumen_id);
                    break;
                case 'LPPR' :
                    $file_name = 'LapKuantitasPenjRekap';
                    $objPHPExcel = self::LapKuantitasPenj($format, $from_date, $to_date, $jnstrans, false,
                        $supplier_id, $salesman_id, $pasar_id, $konsumen_id, false);
                    break;
                case 'LP' :
                    $file_name = 'LapPenj';
                    $objPHPExcel = self::LapKuantitasPenj($format, $from_date, $to_date, $jnstrans, true,
                        $supplier_id, $salesman_id, $pasar_id, $konsumen_id);
                    break;
                case 'LPR' :
                    $file_name = 'LapPenjRekap';
                    $objPHPExcel = self::LapKuantitasPenj($format, $from_date, $to_date, $jnstrans, true,
                        $supplier_id, $salesman_id, $pasar_id, $konsumen_id, false);
                    break;
                case 'LDN' :
                    $file_name = 'LapDetilNota';
                    $objPHPExcel = self::LapDetilNota($format, $from_date, $to_date, $jnstrans,
                        $salesman_id, $supplier_id, $pasar_id, $konsumen_id);
                    break;
            }
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, $file_name);
            }
            Yii::app()->end();
        }
    }
    protected function LapKuantitasPenj($format = 'excel', $from, $to, $jnstrans, $internal = false,
                                        $supp_id = null, $sales_id = null, $pasar_id = null,
                                        $konsumen_id = null, $tgl = true)
    {
        $objPHPExcel = new PHPExcel();
        PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
//        $objPHPExcel->getDefaultStyle()->getFont()->setName('Georgia')->setSize(10);
        $start = 1;
        $supp_null = $supp_id == null;
        $sales_null = $sales_id == null;
        $nama_report = $tgl ? "" : "Rekap";
        $worksheet_name = $internal ? "Laporan Penjualan $nama_report" : "Laporan Kuantitas Penjualan $nama_report";
        if (!$supp_null) {
            $suplier = $this->loadModel($supp_id, 'Supplier');
        }
        $this->header($objPHPExcel, $start, '', strtoupper($worksheet_name));
        $objPHPExcel->setActiveSheetIndex(0)
            ->mergeCells("A$start:D$start")
            ->setCellValue("A$start", "Dari " . sql2long_date($from) . " Sampai " . sql2long_date($to))
            ->getStyle("A$start")->getFont()->setSize(10)->setBold(false);
        if (!$supp_null) {
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("A$start:D$start")
                ->setCellValue("A$start", "Supplier")
                ->setCellValue("B$start", ": $suplier->supplier_name")
                ->getStyle("A$start")->getFont()->setSize(10)->setBold(true);
            $start++;
        }
        $start++;
        $row = U::get_report_pnjl($from, $to, $jnstrans, $internal, $supp_id, $sales_id, $pasar_id, $konsumen_id, $tgl);
        $header = array_keys($row[0]);
        if ($header == null) {
            return $objPHPExcel;
        }
        $jml_header = count($header);
        $col_end = chr(64 + $jml_header);
        $start_row = $start + 1;
        for ($i = 0; $i < $jml_header; $i++) {
            $col = chr(65 + $i);
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING);
            //->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
        }
        $start_body = $start_row;
        for ($r = 0; $r < count($row); $r++) {
            $objPHPExcel->setActiveSheetIndex(0)
                ->fromArray($row[$r], null, "A$start_body");
            $start_body++;
        }
        for ($i = 0; $i < $jml_header; $i++) {
            $col = chr(65 + $i);
            if ($header[$i] == 'Penjualan Bruto' || $header[$i] == 'Potongan' || $header[$i] == 'Penjualan Neto') {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                } else {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                }
            } elseif ($header[$i] == 'Kuantitas Penjualan' || $header[$i] == 'Kuantitas Jual (pieces)') {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED3);
                } else {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                }
            } elseif ($header[$i] == 'Tanggal') {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
                }
            } else {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                }
            }
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
        }
        $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
        $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
        $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
        return $objPHPExcel;
    }
    protected function LapDetilNota($format = 'excel', $from, $to, $jnstrans, $sales_id, $supp_id = null, $pasar_id = null,
                                    $konsumen_id = null)
    {
        $objPHPExcel = new PHPExcel();
        PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
//        $objPHPExcel->getDefaultStyle()->getFont()->setName('Georgia')->setSize(10);
        $start = 1;
        $worksheet_name = "LaporanDetilNota";
        $this->header($objPHPExcel, $start, $worksheet_name,
            "DETIL NOTA");
        $objPHPExcel->setActiveSheetIndex(0)
            ->mergeCells("A$start:D$start")
            ->setCellValue("A$start", "Dari " . sql2long_date($from) . " Sampai " . sql2long_date($to))
            ->getStyle("A$start")->getFont()->setSize(10)->setBold(false);
        $start++;
        $start++;
        $row = U::get_report_detil_nota($from, $to, $jnstrans, $sales_id, $supp_id, $pasar_id, $konsumen_id);
        $header = array_keys($row[0]);
        $jml_header = count($header);
        $col_end = chr(63 + $jml_header);
        $start_row = $start + 1;
        $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
        $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
        $grand_total = 0;
        $col_nominal = '';
        for ($i = 0; $i < $jml_header; $i++) {
            $col = chr(65 + $i);
            if ($header[$i] == 'total') {
                continue;
            }
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING)
                ->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
            if ($header[$i] == 'Nominal') {
                $col_nominal = $col;
            }
        }
        $start_body = $start_row;
        $row_counter = '';
        $jml_row = count($row);
        for ($r = 0; $r < $jml_row; $r++) {
            if ($row_counter != $row[$r]["No Faktur"]) {
                $row_counter = $row[$r]["No Faktur"];
            }
            $objPHPExcel->setActiveSheetIndex(0)
                ->fromArray($row[$r], null, "A$start_body");
            if ($r + 1 >= $jml_row) {
                $start_body++;
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue("A$start_body", 'Sub Total ')
                    ->setCellValue("$col_nominal$start_body", $row[$r]['total']);
                //->getStyle("A$start_body")->getFont()->setSize(8)->setBold(true);
                $grand_total += $row[$r]['total'];
            } elseif ($row_counter != $row[$r + 1]["No Faktur"]) {
                $start_body++;
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue("A$start_body", 'Sub Total ')
                    ->setCellValue("$col_nominal$start_body", $row[$r]['total']);
                //->getStyle("A$start_body")->getFont()->setSize(8)->setBold(true);
                $grand_total += $row[$r]['total'];
            }
            $start_body++;
        }
        for ($i = 0; $i < $jml_header; $i++) {
            $col = chr(65 + $i);
            if ($header[$i] == 'Kuantitas Penjualan' || $header[$i] == 'Kuantitas Jual (pieces)' ||
                $header[$i] == 'Potongan' || $header[$i] == 'Nominal'
            ) {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                } else {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                }
            } elseif ($header[$i] == 'Tanggal') {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
                }
            } elseif ($header[$i] == 'Disc 1' || $header[$i] == 'Disc 2') {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
                }
            } elseif ($header[$i] == 'total') {
                $objPHPExcel->setActiveSheetIndex(0)->removeColumn($col, 1);
            } else {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                }
            }
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
        }
        $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue("A$start_body", 'Grand Total ')
            ->setCellValue("$col_nominal$start_body", $grand_total)
            ->getStyle("A$start_body")->getFont()->setSize(8)->setBold(true);
        return $objPHPExcel;
    }
    public function actionLabaKotorSales()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $from = $_POST['from_date'];
            $to = $_POST['to_date'];
            $salesman_id = $_POST['salesman_id'];
            $salesman = $this->loadModel($salesman_id, 'Salesman');
            $objPHPExcel = new PHPExcel();
            PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
            $start = 1;
            $worksheet_name = "LaporanLabaKotor";
            $file_name = $worksheet_name . $salesman->salesman_name;
            $this->header($objPHPExcel, $start, $worksheet_name,
                "LABA KOTOR PER SALESMAN");
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("A$start:D$start")
                ->setCellValue("A$start", "Dari " . sql2long_date($from) . " Sampai " . sql2long_date($to))
                ->getStyle("A$start")->getFont()->setSize(10)->setBold(false);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue("A$start", "Salesman")
                ->setCellValue("B$start", ": $salesman->salesman_name")
                ->getStyle("A$start")->getFont()->setSize(10)->setBold(true);
            $start++;
            $start++;
            $row = U::get_report_laba_kotor($from, $to, $salesman_id);
            $header = array_keys($row[0]);
            $jml_header = count($header);
            $col_end = chr(64 + $jml_header);
            $start_row = $start + 1;
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING)
                    ->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
            }
            $start_body = $start_row;
            for ($r = 0; $r < count($row); $r++) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->fromArray($row[$r], null, "A$start_body");
                $start_body++;
            }
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                for ($r = 0; $r < count($row); $r++) {
                    if ($header[$i] == 'Penjualan Bruto' || $header[$i] == 'Potongan' ||
                        $header[$i] == 'Penjualan Netto' || $header[$i] == 'HPP' || $header[$i] == 'Laba Kotor'
                    ) {
                        if ($format == 'excel') {
                            $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                                ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                        } else {
                            $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                        }
                    } elseif ($header[$i] == 'Tanggal') {
                        if ($format == 'excel') {
                            $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                                ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
                        }
                    } else {
                        if ($format == 'excel') {
                            $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                                ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                        }
                    }
                }
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
        }
        if ($format == 'excel') {
            self::footerExcel($objPHPExcel, $file_name);
        } else {
            self::footerPdf($objPHPExcel, 'A4', $file_name, $file_name);
        }
        Yii::app()->end();
    }
    public function actionLapKasMasuk()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $file_name = 'LaporanKasMasuk';
            $format = $_POST['format'];
            $tgl = $_POST['tgl'];
            $sales = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $sales_name = str_replace(" ", "", $sales->salesman_name);
            $objPHPExcel = new PHPExcel();
            PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
//            $objPHPExcel->getDefaultStyle()->getFont()->setName('Georgia')->setSize(10);
            $start = 1;
            $worksheet_name = "LaporanKasMasuk";
            $this->header($objPHPExcel, $start, $worksheet_name,
                "L A P O R A N  K A S  M A S U K");
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", "Tanggal: " . sql2long_date($tgl));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start",
                    "Salesman: " . $sales_name);
            $start++;
            $start++;
            $row = U::get_report_kas_masuk($tgl, $_POST['salesman_id']);
            $header = array_keys($row[0]);
            $jml_header = count($header);
            $col_end = chr(64 + $jml_header);
            $start_row = $start + 1;
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING)
                    ->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            $start_body = $start_row;
            for ($r = 0; $r < count($row); $r++) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->fromArray($row[$r], null, "A$start_body");
                $start_body++;
            }
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                if ($header[$i] == 'NILAI' || $header[$i] == 'KAS DITERIMA TGL INI') {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    }
                } elseif ($header[$i] == 'Tgl') {
//                    if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
//                    }
                } else {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    }
                }
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, 'LaporanKasMasuk');
            }
            Yii::app()->end();
        }
    }
    public function actionLapDaftarPiutang()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $file_name = 'LapDaftarPiutang';
            $format = $_POST['format'];
            $objPHPExcel = self::LapPiutangAll($format, $_POST['tgl']);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, 'LapDaftarPiutang');
            }
            Yii::app()->end();
        }
    }
    protected function LapPiutangAll($format = 'excel', $date)
    {
        $objPHPExcel = new PHPExcel();
        PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
//        $objPHPExcel->getDefaultStyle()->getFont()->setName('Georgia')->setSize(10);
        $start = 1;
        $worksheet_name = "LaporanDaftarPiutang";
        $this->header($objPHPExcel, $start, $worksheet_name,
            "DAFTAR PIUTANG");
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue("A$start", "Tanggal ")
            ->setCellValue("B$start", ": " . sql2long_date($date))
            ->getStyle("A$start")->getFont()->setSize(10)->setBold(false);
        $start++;
        $start++;
        $row = U::get_report_piutang_all($date);
        $header = array_keys($row[0]);
        $jml_header = count($header);
        $col_end = chr(64 + $jml_header);
        $start_row = $start + 1;
        $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
        $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
        for ($i = 0; $i < $jml_header; $i++) {
            $col = chr(65 + $i);
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue("$col$start", $header[$i])
                ->getStyle("$col$start")->getFont()->setBold(true);
        }
        $start_body = $start_row;
        for ($r = 0; $r < count($row); $r++) {
            $objPHPExcel->setActiveSheetIndex(0)
                ->fromArray($row[$r], null, "A$start_body");
            $start_body++;
        }
        for ($i = 0; $i < $jml_header; $i++) {
            $col = chr(65 + $i);
            if ($header[$i] == 'Nilai Faktur' || $header[$i] == 'Sisa Tagihan') {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                } else {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                }
            } elseif ($header[$i] == 'Tgl Faktur' || $header[$i] == 'Tgl Jatuh Tempo') {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
                }
            } else {
                if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                }
            }
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
        }
        $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
        return $objPHPExcel;
    }
    public function actionLapMasterKonsumen()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $file_name = 'DaftarMasterKonsumen';
            $format = $_POST['format'];
            $objPHPExcel = new PHPExcel();
            PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
//            $objPHPExcel->getDefaultStyle()->getFont()->setName('Georgia')->setSize(10);
            $start = 1;
            $worksheet_name = "DaftarMasterKonsumen";
            $this->header($objPHPExcel, $start, $worksheet_name,
                "DAFTAR MASTER KONSUMEN");
            $start++;
            $start++;
            $row = U::get_daftar_master_konsumen($_POST['konsumen_code'], $_POST['konsumen_name'],
                $_POST['phone'], $_POST['hp'], $_POST['hp2'], $_POST['tempo'], $_POST['status']);
            $header = array_keys($row[0]);
            $jml_header = count($header);
            $col_end = chr(64 + $jml_header);
            $start_row = $start + 1;
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING)
                    ->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            $start_body = $start_row;
            for ($r = 0; $r < count($row); $r++) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->fromArray($row[$r], null, "A$start_body");
                $start_body++;
            }
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                if ($header[$i] == 'Tempo' || $header[$i] == 'Status') {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    }
                } else {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    }
                }
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, 'LapDaftarPiutang');
            }
            Yii::app()->end();
        }
    }
    public function actionLapPengiriman()
    {
        if (Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $file_name = 'LaporanPengiriman';
            $format = $_POST['format'];
            $tgl = $_POST['tgl'];
            $sales = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $sales_name = str_replace(" ", "", $sales->salesman_name);
            $objPHPExcel = new PHPExcel();
            PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
//            $objPHPExcel->getDefaultStyle()->getFont()->setName('Georgia')->setSize(10);
            $start = 1;
            $worksheet_name = "LaporanPengiriman";
            $this->header($objPHPExcel, $start, $worksheet_name,
                "L A P O R A N  P E N G I R I M A N");
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", "Tanggal: " . sql2long_date($tgl));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start",
                    "Salesman: " . $sales_name);
            $start++;
            $start++;
            $row = U::report_pengiriman($tgl, $_POST['salesman_id']);
            $header = array_keys($row[0]);
            $jml_header = count($header);
            $col_end = chr(64 + $jml_header);
            $start_row = $start + 1;
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("$col$start", $header[$i], PHPExcel_Cell_DataType::TYPE_STRING)
                    ->getStyle("$col$start")->getFont()->setSize(8)->setBold(true);
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            $start_body = $start_row;
            for ($r = 0; $r < count($row); $r++) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->fromArray($row[$r], null, "A$start_body");
                $start_body++;
            }
            for ($i = 0; $i < $jml_header; $i++) {
                $col = chr(65 + $i);
                if ($header[$i] == 'Netto') {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getAlignment()
                            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    }
                } elseif ($header[$i] == 'Tgl') {
//                    if ($format == 'excel') {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
//                    }
                } else {
                    if ($format == 'excel') {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle("$col$start_row:$col$start_body")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    }
                }
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($col)->setAutoSize(true);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:$col_end$start")->applyFromArray($borderbottom);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, 'LapPengiriman');
            }
            Yii::app()->end();
        }
    }
    public function actionPrintFakturJual()
    {
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $isexcel = $format == "excel";
            $jual = $this->loadModel($_POST['penjualan_id'], 'Penjualan');
            $start = 1;
            $file_name = "FakturPenjualan_" . $jual->doc_ref;
            $worksheet_name = 'FakturPenjualan';
            $objPHPExcel = new PHPExcel();
            $this->header($objPHPExcel, $start, $worksheet_name,
                html_entity_decode("F&nbsp;&nbsp;A&nbsp;&nbsp;K&nbsp;&nbsp;T&nbsp;&nbsp;U&nbsp;&nbsp;R&nbsp;&nbsp;&nbsp;&nbsp;P&nbsp;&nbsp;E&nbsp;&nbsp;N&nbsp;&nbsp;J&nbsp;&nbsp;U&nbsp;&nbsp;A&nbsp;&nbsp;L&nbsp;&nbsp;A&nbsp;&nbsp;N", ENT_QUOTES, 'UTF-8'),
//                PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
                'Primasari');
            $objPHPExcel->setActiveSheetIndex(0)->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
            $objPHPExcel->setActiveSheetIndex(0)->getPageSetup()->setScale(90);
            $start--;
            $this->printParamArr($objPHPExcel, $start, 'C', 10, app()->params['corp']['address']);
            $start--;
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("H$start:J$start")
                ->setCellValue("H$start", 'Kepada Yth :');
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("H$start:J$start")
                ->mergeCells("D$start:F$start")
                ->setCellValueExplicit("D$start", html_entity_decode('Tgl. Faktur&nbsp;&nbsp;&nbsp;&nbsp;: ' .
                    sql2date($jual->tgl, 'dd MMM yyyy'), ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("H$start", strtoupper($jual->konsumen->konsumen_code),
                    PHPExcel_Cell_DataType::TYPE_STRING);
            $start++;
            $pembayaran = $jual->tgl == $jual->tempo ? 'Tunai' : 'Kredit';
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("A$start:C$start")
                ->mergeCells("D$start:F$start")
                ->setCellValue("D$start", 'Jatuh Tempo : ' . sql2date($jual->tempo, 'dd MMM yyyy'))
                ->mergeCells("H$start:J$start")
                ->setCellValue("A$start", html_entity_decode('NPWP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;: ' .
                    app()->params['corp']['npwp'], ENT_QUOTES, 'UTF-8'))
                ->setCellValue("H$start", strtoupper($jual->konsumen->konsumen_name));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("A$start:C$start")
                ->mergeCells("H$start:J$start")
                ->mergeCells("D$start:F$start")
                ->setCellValue("D$start", 'No. Faktur : ' . $jual->doc_ref)
                ->setCellValue("A$start", html_entity_decode('Telp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;: ' .
                    app()->params['corp']['telp'], ENT_QUOTES, 'UTF-8'))
                ->setCellValue("H$start", strtoupper($jual->konsumen->area->area_name));
//            $start++;
//            $end_address = $start + 2;
//            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("A$start:F$start")
//                ->mergeCells("H$start:J$end_address")
//                ->setCellValue("A$start", html_entity_decode('Tgl. Faktur&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;: ' .
//                    sql2date($jual->tgl, 'dd MMM yyyy') . "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No. Faktur : " .
//                    $jual->doc_ref, ENT_QUOTES, 'UTF-8'))
//                ->setCellValue("H$start", strtoupper($jual->konsumen->address));
//            $objPHPExcel->setActiveSheetIndex(0)->getStyle("H$start")->getAlignment()
//                ->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
//            $start++;
//
//            $objPHPExcel->setActiveSheetIndex(0)
//                ->mergeCells("A$start:F$start")
//                ->setCellValue("A$start", 'Jatuh Tempo : ' . sql2date($jual->tempo, 'dd MMM yyyy') .
//                    html_entity_decode("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pembayaran : $pembayaran", ENT_QUOTES, 'UTF-8'));
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("A$start:C$start")
                ->mergeCells("D$start:F$start")
                ->setCellValue("D$start", "Pembayaran : $pembayaran")
                ->mergeCells("H$start:J$start")
                ->setCellValue("H$start", strtoupper(str_replace("\n", " ", $jual->konsumen->address)))
                ->setCellValueExplicit("A$start",
                    html_entity_decode('Salesman : ' . $jual->salesman->salesman_name, ENT_QUOTES, 'UTF-8'),
                    PHPExcel_Cell_DataType::TYPE_STRING);
            $start++;
            $bordertop = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_DOTTED)));
            $borderbottom = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_DOTTED)));
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("B$start:C$start")
                ->setCellValueExplicit("A$start",
                    html_entity_decode("&nbsp;NO.", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
//                ->setCellValue("B$start", 'KODE')
                ->setCellValueExplicit("B$start",
                    html_entity_decode("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;N A M A   B A R A N G&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("D$start",
                    html_entity_decode("&nbsp;&nbsp;SAT&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("E$start",
                    html_entity_decode("JML&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("F$start",
                    html_entity_decode("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;HARGA&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("G$start",
                    html_entity_decode("DISC1&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("H$start",
                    html_entity_decode("DISC2&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("I$start",
                    html_entity_decode("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;POT&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("J$start",
                    html_entity_decode("&nbsp;&nbsp;&nbsp;&nbsp;NOMINAL", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("E$start:J$start")->getAlignment()
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:J$start")->applyFromArray($bordertop);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:J$start")->applyFromArray($borderbottom);
            $criteria = new CDbCriteria();
            $criteria->addCondition("penjualan_id = :penjualan_id");
            $criteria->params = array(':penjualan_id' => $_POST['penjualan_id']);
            $detilJual = DetilPenjualan::model()->findAll($criteria);
            $detil_row = $start + 1;
            $row_normalize = 7;
            if ($isexcel) {
                foreach ($detilJual as $key => $item) {
                    $start++;
                    $no = $key + 1;
                    $objPHPExcel->setActiveSheetIndex(0)
                        ->mergeCells("B$start:C$start")
                        ->setCellValueExplicit("A$start",
                            html_entity_decode("&nbsp;$no.", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValue("B$start", $item->barang->barang_name)
                        ->setCellValue("D$start", $item->sat)
                        ->setCellValue("E$start", $item->jml)
                        ->setCellValue("F$start", $item->price)
                        ->setCellValue("G$start", $item->disc1)
                        ->setCellValue("H$start", $item->disc2)
                        ->setCellValue("I$start", $item->pot)
                        ->setCellValue("J$start", $item->nominal);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("B$start:D$start")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle("E$start:J$start")->getNumberFormat()
                        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
                    $row_normalize--;
                }
            } else {
                foreach ($detilJual as $key => $item) {
                    $start++;
                    $no = $key + 1;
                    $objPHPExcel->setActiveSheetIndex(0)
                        ->mergeCells("B$start:C$start")
                        ->setCellValueExplicit("A$start",
                            html_entity_decode("&nbsp;$no.", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("B$start",
                            html_entity_decode(substr($item->barang->barang_name, 0, 30), ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("D$start",
                            html_entity_decode($item->sat, ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("E$start",
                            html_entity_decode(number_format($item->jml) . "&nbsp;", ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("F$start",
                            html_entity_decode(number_format($item->price) . "&nbsp;", ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("G$start",
                            html_entity_decode(percent_format($item->disc1), ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("H$start",
                            html_entity_decode(percent_format($item->disc2), ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("I$start",
                            html_entity_decode(number_format($item->pot) . "&nbsp;", ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING)
                        ->setCellValueExplicit("J$start",
                            html_entity_decode(number_format($item->nominal), ENT_QUOTES, 'UTF-8'),
                            PHPExcel_Cell_DataType::TYPE_STRING);
                    $row_normalize--;
                }
            }
            for ($i = $row_normalize; $i > 0; $i--) {
                $start++;
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("A$start",
                        html_entity_decode("&nbsp;", ENT_QUOTES, 'UTF-8'), PHPExcel_Cell_DataType::TYPE_STRING);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("E$detil_row:J$start")->getAlignment()
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start:J$start")->applyFromArray($borderbottom);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("F$start:G$start")
                ->mergeCells("H$start:J$start")
                ->setCellValue("C$start", 'Di Terima Pelanggan :')
                ->setCellValue("F$start", 'Dikirim Oleh :')
                ->setCellValue("H$start", 'Total : ' . number_format($jual->total))
//                ->setCellValue("J$start", )
                ->getStyle("H$start:J$start")->getFont()->setSize(14);
//            $objPHPExcel->setActiveSheetIndex(0)->getStyle("J$start")->getNumberFormat()
//                ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED0);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("H$start")->getAlignment()
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            $start++;
            $objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
//            $start++;
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue("A$start", ' ');
            $start++;
//            $objPHPExcel->setActiveSheetIndex(0)
//                ->setCellValue("A$start", ' ');
//            $start++;
            $start_body = $start;
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("F$start:G$start")
                ->setCellValue("C$start", 'Nama & Cap Tgl. : ............')
                ->setCellValue("F$start", 'Nama & Tgl. : ............');
            $this->printParamArr($objPHPExcel, $start, 'F', 10, app()->params['corp']['faktur_footer']);
            $objPHPExcel->setActiveSheetIndex(0)
                ->mergeCells("G$start:J$start")
                ->setCellValue("G$start", "Tgl. : " . get_date_today('dd-MM-yyyy') .
                    " / " . get_time_now() . " / " . $user = Yii::app()->user->name);
            $start++;
            $end_body = $start - 1;
            $styleArray = array('borders' => array('diagonal' => array('style' => PHPExcel_Style_Border::BORDER_DOTTED)));
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start_body:B$end_body")->applyFromArray($styleArray);
            PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("A")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("B")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("C")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("D")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("E")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("F")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("G")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("H")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("I")->setAutoSize(true);
            $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("J")->setAutoSize(true);
//            $format ='PDF';
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } elseif ($format == 'pdf') {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, 'Faktur Penjualan', 'P');
            } else {
                if (Yii::app()->request->isAjaxRequest) {
                    $objPHPExcel->getActiveSheet()->setShowGridlines(false);
//                    $mPDF1 = new mPDF('', "$paper_size-L", 8, 'Courier New', 5, 5, 5, 5, 5, 5, 'L');
                    $objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
                    $header = $objWriter->generateHTMLHeader(false);
//                    $header = str_replace("<body style='background-color:white;'>", "<body onload='window.print();'>",
//                        $header);
                    $header = str_replace("Untitled Spreadsheet", "FakturPenjualan" . $jual->doc_ref, $header);
//                    $url_a5 = Yii::app()->request->baseUrl . '/css/a5.css';
//                    $header = str_replace("</head>", "<link rel='stylesheet' type='text/css' href='$url_a5'\n</head>", $header);
                    $objWriter->setUseInlineCSS(true);
//                    $html = $header . $objWriter->generateStyles(true) . $objWriter->generateSheetData() .
//                        $objWriter->generateHTMLFooter();
                    echo CJSON::encode(array(
                        'success' => true,
                        'msg' => $objWriter->generateStyles(true) . str_replace('<table', '<table width="800"', $objWriter->generateSheetData())
                    ));
                }
            }
            Yii::app()->end();
        }
    }
    public function actionPrintDOFinal()
    {
        if (isset($_POST) && !empty($_POST)) {
            $format = $_POST['format'];
            $tgl = $_POST['tgl'];
            $sales = $this->loadModel($_POST['salesman_id'], 'Salesman');
            $sales_name = str_replace(" ", "", $sales->salesman_name);
            $criteria = new CDbCriteria();
            $criteria->addCondition("salesman_id = :salesman_id");
            $criteria->addCondition("tgl = :tgl");
            $criteria->params = array(':salesman_id' => $_POST['salesman_id'], ':tgl' => $_POST['tgl']);
            $dof = Dof::model()->find($criteria);
            $dof_detil = U::print_dof_gudang($dof->dof_id);
            $start = 1;
            $file_name = "DOFinal_" . $sales_name . "_" . $tgl;
            $worksheet_name = 'DOFinal';
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);
            $objPHPExcel->setActiveSheetIndex(0)->getPageSetup()->setPaperSize("A4");
            $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setLeft(0.1 / 2.54);
            $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setRight(0.1 / 2.54);
            $objPHPExcel->setActiveSheetIndex(0)->getPageMargins()->setTop(0.1 / 2.54);
            $objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start",
                    "Salesman : " . $sales->salesman_name)
                ->getStyle("A$start")->getFont()->setSize(10)->setBold(false);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", "Tanggal : " . sql2long_date($tgl))
                ->getStyle("A$start")->getFont()->setSize(10)->setBold(false);
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:G$start")
                ->setCellValue("A$start", " ");
            $start++;
            $start_body = $start;
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValueExplicit("A$start", h_e_d("Kode Barang&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("B$start", h_e_d("Nama Barang&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("C$start", h_e_d("&nbsp;&nbsp;Barang Baik&nbsp;&nbsp;"), PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit("D$start", h_e_d("&nbsp;&nbsp;Barang Rusak&nbsp;&nbsp;"), PHPExcel_Cell_DataType::TYPE_STRING)
                ->getStyle("A$start:D$start")->getFont()->setBold(true);
            foreach ($dof_detil as $row) {
                $start++;
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueExplicit("A$start", h_e_d($row['Kode Barang'] . '&nbsp;'), PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValueExplicit("B$start", h_e_d($row['Nama Barang'] . '&nbsp;'), PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue("C$start", $row['Jumlah Barang Baik'])
                    ->setCellValue("D$start", $row['Jumlah Barang Rusak']);
            }
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("C$start_body:D$start")->getAlignment()
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            $styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
            $objPHPExcel->setActiveSheetIndex(0)->getStyle("A$start_body:D$start")->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
            if ($format == 'excel') {
                $this->footerExcel($objPHPExcel, $file_name);
            } else {
                $this->footerPdf($objPHPExcel, 'A4', $file_name, $file_name);
            }
            Yii::app()->end();
        }
    }
    protected function printParamArr(&$objPHPExcel, &$start, $merge, $fontsize, $param)
    {
        foreach ($param as $almt) {
            $start++;
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A$start:$merge$start")
                ->setCellValue("A$start", $almt)
                ->getStyle("A$start")->getFont()->setSize($fontsize);
        }
    }
    protected function beforeAction($event)
    {
        ini_set("memory_limit", "256M");
        ini_set("max_execution_time", "0");
        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3;
        $cacheSettings = array('memoryCacheSize' => '32MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
//        PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
        return true;
    }
}